import pandas as pd
from io import BytesIO

from rest_framework.views import APIView
from rest_framework.permissions import AllowAny, IsAuthenticated
from rest_framework_simplejwt.authentication import JWTAuthentication
from rest_framework.exceptions import ValidationError
from drf_yasg import openapi
from drf_yasg.utils import swagger_auto_schema

from apps.ExamManagement.models import Exam, ExamDepart
from apps.ResultManagement.models import Results, ResultQuestionSolution
from .models import Grade
from .serializers import GradeSerializer, DetailGradeSerializer

from ..PaperManagement.models import PaperQuestion
from ..utils.pageNumberPagination import CustomPageNumberPagination
from ..UserManagement.models import User
from django.http import HttpResponse
from django.utils.encoding import escape_uri_path
from django.db import DatabaseError
from datetime import datetime

from ..utils.response_utils import api_response, ResponseCode


class CreateGrade(APIView):
    """
    创建成绩报表
    """
    authentication_classes = [JWTAuthentication]
    permission_classes = [IsAuthenticated]

    @swagger_auto_schema(
        operation_description="创建成绩报表接口",
        request_body=openapi.Schema(
            type=openapi.TYPE_OBJECT,
            required=['exam_id'],
            properties={
                'exam_id': openapi.Schema(type=openapi.TYPE_STRING, description='考试ID'),
            },
        ),
        responses={
            200: openapi.Response(
                description="成功返回成绩报表和题目正确率",
                schema=GradeSerializer(many=True)
            ),
            400: openapi.Response('请求参数不合法'),
            409: openapi.Response('成绩报表已存在'),
            404: openapi.Response('成绩报表不存在'),
        },
        tags=["成绩报表管理"]
    )
    def post(self, request):
        try:
            exam_id = request.data.get('exam_id')
            exams = []
            grades_correct_rates = []
            exam = Exam.objects.filter(id=exam_id).first()
            if not exam:
                return api_response(ResponseCode.NOT_FOUND, '考试不存在')
            exams.append(exam)

            # 循环拿到子级考试
            sons = Exam.objects.filter(parent=exam).order_by('created_at')
            for son in sons:
                if son:
                    exams.append(son)

            for exam in exams:
                # 获取试卷所有试题
                paper = exam.paper
                paper_questions = PaperQuestion.objects.filter(paper=paper)

                correct_rates = []

                for paper_question in paper_questions:
                    question = paper_question.question
                    if not question:
                        return api_response(ResponseCode.NOT_FOUND, '题目不存在')

                    results = Results.objects.filter(exam=exam)
                    question_score = 0
                    # 统计试题得分
                    for result in results:
                        results_question = ResultQuestionSolution.objects.filter(results=result,
                                                                                 question=question).first()
                        question_score += results_question.score

                    # 试题在某个试卷中的分值
                    paper_question_target = PaperQuestion.objects.filter(paper=paper, question=question).first()

                    # 统计正确率
                    result_profile = Results.objects.filter(exam=exam).first()
                    if not result_profile:  # 如果还未创建过成绩单
                        correct_rate = 0
                    else:
                        correct_rate = question_score / (len(results) * paper_question_target.score) * 100
                        correct_rate = round(correct_rate, 2)
                    correct_rates.append(correct_rate)

                ############################################
                grade = Grade.objects.filter(exam=exam).first()
                if not grade:
                    results = Results.objects.filter(exam=exam)
                    passes = 0  # 通过人数
                    score = 0  # 分数
                    should_num = 0  # 应考人数

                    no_result = 0
                    for result in results:
                        score += result.total_score
                        if result.total_score >= (exam.paper.total_score * 0.6):
                            passes += 1

                        # 未参加考试也有答题卡提交    实考人数要减去答题卡中 solution全为null的答题卡
                        result_profiles = ResultQuestionSolution.objects.filter(results=result)

                        # 初始值设为0
                        if_result = 0
                        for result_profile in result_profiles:
                            # if result_profile.solution != '':         # 如果有答题内容，代表参加了考试
                            #     if_result = 1
                            if result_profile.solution and result_profile.solution.strip():
                                if_result = 1
                                break  # 一旦有答题就可以退出循环
                        # 到最后仍然是0，代表未参加考试
                        if if_result == 0:
                            no_result += 1


                    # 首次考试，通过部门统计应考人数
                    if exam.number == 1:
                        exam_departs = ExamDepart.objects.filter(exam=exam)
                        for exam_depart in exam_departs:
                            users = User.objects.filter(depart=exam_depart.depart)
                            should_num += len(users)
                    # 补考
                    if exam.number > 1:
                        exam_users = ExamDepart.objects.filter(exam=exam)
                        should_num = len(exam_users)

                    actual_num = len(results) - no_result  # 实考人数
                    if actual_num == 0:
                        pass_rate = 0
                        average_score = 0
                    else:
                        pass_rate = passes / actual_num * 100  # 通过率
                        average_score = score / actual_num  # 平均分

                    grade = Grade.objects.create(exam=exam, actual_num=actual_num, should_num=should_num,
                                                 average_score=average_score, pass_rate=pass_rate)

                grade_correct_rates = [grade, correct_rates]
                grades_correct_rates.append(grade_correct_rates)

            serializer = GradeSerializer(grades_correct_rates, many=True)

            return api_response(ResponseCode.SUCCESS, '成绩报表获取成功', serializer.data)
        except ValidationError as e:
            return api_response(ResponseCode.BAD_REQUEST, f"请求参数错误: {str(e)}")
        except Exception as e:
            return api_response(ResponseCode.INTERNAL_SERVER_ERROR, f"服务器内部错误: {str(e)}")

#
# class CreateGradeDetail(APIView):
#     """
#             创建题目的错误率
#     """
#     authentication_classes = [JWTAuthentication]
#     permission_classes = [AllowAny]
#
#     @swagger_auto_schema(
#         operation_description="创建题目的错误率接口",
#         request_body=openapi.Schema(
#             type=openapi.TYPE_OBJECT,
#             required=['exam_id'],
#             properties={
#                 'exam_id': openapi.Schema(type=openapi.TYPE_STRING, description='考试ID'),
#             },
#         ),
#         responses={
#             200: openapi.Response(
#                 '创建成功！',
#                 openapi.Schema(
#                     type=openapi.TYPE_OBJECT,
#                     properties={
#                         'id': openapi.Schema(type=openapi.TYPE_STRING, description='题目正确率id'),
#                     }
#                 )
#             ),
#             400: openapi.Response('请求参数不合法'),
#             409: openapi.Response('题目的错误率已存在'),
#             404: openapi.Response('题目的错误率不存在'),
#         },
#         tags=["成绩报表管理"]
#     )
#     def post(self, request):
#         try:
#             exam_id = request.data.get('exam_id')
#             # 在某个考试中的正确率
#             if exam_id:
#                 exams = []
#                 exam = Exam.objects.filter(id=exam_id).first()
#                 if not exam:
#                     return api_response(ResponseCode.NOT_FOUND, '考试不存在')
#
#                 exams.append(exam)
#
#                 # 循环拿到子级考试
#                 sons = Exam.objects.filter(parent=exam).order_by('created_at')
#                 for son in sons:
#                     if son:
#                         exams.append(son)
#
#
#
#                 grade_details = []
#                 for exam in exams:
#                     # 获取试卷所有试题
#                     paper = exam.paper
#                     paper_questions = PaperQuestion.objects.filter(paper=paper).all()
#
#                     correct_rates = []
#
#                     for paper_question in paper_questions:
#                         question = paper_question.question
#                         # order = paper_question.order
#                         if not question:
#                             return api_response(ResponseCode.NOT_FOUND, '题目不存在')
#
#
#                         # 先从数据库中获取
#                         grade_detail = GradeDetail.objects.filter(question=question, exam=exam).first()
#                         if not grade_detail:
#                             results = Results.objects.filter(exam=exam).all()
#                             question_score = 0
#                             for result in results:
#                                 results_question = ResultQuestionSolution.objects.filter(results=result, question=question).first()
#                                 question_score += results_question.score
#
#                             # 试题在某个试卷中的分值
#                             paper = exam.paper
#                             paper_question = PaperQuestion.objects.filter(paper=paper, question=question).first()
#
#                             # 统计正确率
#                             result_profile = Results.objects.filter(exam=exam).first()
#                             if not result_profile:      # 如果还未创建过成绩单
#                                 correct_rate = 0
#                             else:
#                                 correct_rate =  question_score / (len(results) * paper_question.score) * 100
#                             correct_rates.append(correct_rate)
#
#                             grade_detail = GradeDetail.objects.create(question=question, exam=exam, correct_rate=correct_rate)
#
#                         grade_details.append(grade_detail)
#
#                 serializer = ExamGradeDetailSerializer(grade_details, many=True)
#                 return api_response(ResponseCode.SUCCESS, '题目错误率获取成功', serializer.data)
#
#
#         except ValidationError as e:
#             return api_response(ResponseCode.BAD_REQUEST, f"请求参数错误: {str(e)}")
#         except Exception as e:
#             return api_response(ResponseCode.INTERNAL_SERVER_ERROR, f"服务器内部错误: {str(e)}")

# 在所有 有该试题的考试 中的正确率
# else:
#     grade_detail = GradeDetail.objects.filter(question=question).first()
#     if not grade_detail:
#         question_score = 0           # 试题得分
#         question_total_score = 0     # 试题总分
#         # 拿到所有 有该试题的 试卷
#         paper_questions = PaperQuestion.objects.filter(question=question).all()
#         for paper_question in paper_questions:
#             # 该试题在该试卷上的分值
#             paper_question_score = paper_question.score
#             # 拿到所有 用该试卷的 考试
#             exams = Exam.objects.filter(paper=paper_question.paper).all()
#             for exam in exams:
#                 # 拿到该考试 所有的 答题卡
#                 results = Results.objects.filter(exam=exam).all()
#                 for result in results:
#                     results_question = ResultQuestionSolution.objects.filter(results=result, question=question).first()
#                     question_score += results_question.score
#                     question_total_score += paper_question_score
#
#         correct_rate = question_score / question_total_score * 100
#         grade_detail = GradeDetail.objects.create(question=question, correct_rate=correct_rate)
#
#     serializer = GradeDetailSerializer(grade_detail)
#     return api_response(ResponseCode.SUCCESS, '题目正确率获取成功', serializer.data)


class ExportGrade(APIView):
    """
    导出成绩报表
    """

    authentication_classes = [JWTAuthentication]
    permission_classes = [IsAuthenticated]

    @swagger_auto_schema(
        operation_description="导出成绩报表",
        responses={
            200: openapi.Response(
                description="成功导出成绩报表",
                examples={
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": {
                        "file": "http://example.com/download/grades_data.xlsx"
                    }
                }
            ),
            500: openapi.Response(
                description="服务器内部错误",
                examples={
                    "application/json": {
                        "code": 500,
                        "message": "导出失败，系统错误"
                    }
                }
            ),
        },
        tags=["成绩报表管理"]
    )
    def get(self, request, *args, **kwargs):
        try:
            # 查询所有成绩单
            grades = Grade.objects.all()
            if not grades:
                return api_response(ResponseCode.NOT_FOUND, '没有成绩数据可导出')

            data = []
            for grade in grades:
                data.append(
                    {
                        "id": grade.id,
                        "exam_title": grade.exam.title,
                        "actual_num": grade.actual_num,
                        "should_num": grade.should_num,
                        "average_score": grade.average_score,
                        "pass_rate": grade.pass_rate,
                    }
                )
            # 将数据转换为 pandas DataFrame
            df = pd.DataFrame(data)

            # 将 DataFrame 导出为 Excel 文件（内存中）
            file_stream = BytesIO()
            with pd.ExcelWriter(file_stream, engine='xlsxwriter') as writer:
                df.to_excel(writer, index=False, sheet_name='Grades Data')

            # 返回 Excel 文件流
            file_stream.seek(0)

            # 设置响应头，确保文件作为附件下载
            response = HttpResponse(file_stream,
                                    content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            file_name = "grades_data.xlsx"
            response['Content-Disposition'] = f"attachment; filename*=UTF-8''{escape_uri_path(file_name)}"

            # 返回文件流
            return response

        except DatabaseError:
            return api_response(ResponseCode.INTERNAL_SERVER_ERROR, "数据库错误，无法查询试题数据")

        except Exception as e:
            return api_response(ResponseCode.INTERNAL_SERVER_ERROR, "导出失败，系统错误", str(e))


class DetailGrade(APIView):
    """
    获取成绩单详情
    """
    authentication_classes = [JWTAuthentication]
    permission_classes = [IsAuthenticated]

    @swagger_auto_schema(
        operation_description="获取成绩单详情接口",
        request_body=openapi.Schema(
            type=openapi.TYPE_OBJECT,
            required=['username', 'grade_id'],
            properties={
                'username': openapi.Schema(type=openapi.TYPE_STRING, description='用户姓名'),
                'grade_id': openapi.Schema(type=openapi.TYPE_STRING, description='成绩表ID'),
            },
        ),
        responses={
            200: openapi.Response(
                description="成功返回成绩报表和题目正确率",
                schema=DetailGradeSerializer(many=True)
            ),
            400: openapi.Response('请求参数不合法'),
            409: openapi.Response('成绩单详情已存在'),
            404: openapi.Response('成绩单详情不存在'),
        },
        tags=["成绩报表管理"]
    )
    def post(self, request):
        try:
            keywords = request.data.get('username', '')
            grade_id = request.data.get('grade_id')
            grade_profile = Grade.objects.filter(id=grade_id).first()
            if not grade_profile:
                return api_response(ResponseCode.NOT_FOUND, '成绩单不存在')
            exam_id = grade_profile.exam.id
            page_size = request.query_params.get("pageSize", 10)

            users_by_keywords = User.objects.filter_by_keywords(keywords)
            exam = Exam.objects.filter(id=exam_id).first()
            if not exam:
                return api_response(ResponseCode.NOT_FOUND, '考试不存在')

            # 用户 成绩 错误率 提交时间 答题用时
            users = []
            data = []

            # 首次考试
            if exam.number == 1:
                exam_departs = ExamDepart.objects.filter(exam=exam)
                for exam_depart in exam_departs:

                    users_profile = User.objects.filter(depart=exam_depart.depart)
                    for username in users_profile:
                        user = User.objects.filter(username=username).first()
                        users.append(user)

            # 补考
            if exam.number > 1:
                users = ExamDepart.objects.filter(exam=exam)

            users_target = list(set(users_by_keywords) & set(users))

            for user in users_target:
                dict_user = get_dict_by_user(user, exam)

                data.append(dict_user)

            paginator = CustomPageNumberPagination()
            # 设置分页参数
            paginator.page_size = int(page_size)  # 每页的条数
            paginator.page_query_param = 'pageNo'  # 页码的参数名
            paginator.page_size_query_param = 'page_size'  # 每页数据条数的参数名
            result_page = paginator.paginate_queryset(data, request)
            serializer = DetailGradeSerializer(result_page, many=True)
            paginated_response = paginator.get_paginated_response(serializer.data)

            return api_response(ResponseCode.SUCCESS, '成绩单详情获取成功', paginated_response.data)
        except ValidationError as e:
            return api_response(ResponseCode.BAD_REQUEST, f"请求参数错误: {str(e)}")
        except Exception as e:
            return api_response(ResponseCode.INTERNAL_SERVER_ERROR, f"服务器内部错误: {str(e)}")


def get_dict_by_user(user, exam):
    result = Results.objects.filter(user=user, exam=exam).first()
    if not result:
        total_score = 0
        correct_rate = 100
        created_at = None
        exam_duration = None
    else:
        # 成绩
        total_score = result.total_score

        # 错误率
        paper = exam.paper
        exam_score = paper.total_score
        correct_rate = round((1 - total_score / exam_score) * 100, 2)

        # 提交时间
        created_at = datetime.strptime(result.created_at.strftime("%Y-%m-%d %H:%M:%S"),
                                       "%Y-%m-%d %H:%M:%S")

        # 考试开始时间
        exam_start = datetime.strptime(exam.start_time.strftime("%Y-%m-%d %H:%M:%S"),
                                       "%Y-%m-%d %H:%M:%S")

        # 计算用时（转换为秒）
        exam_duration = (created_at - exam_start).total_seconds() if created_at else None
        hours = int(exam_duration // 3600)  # 整除一小时的秒数
        minutes = int((exam_duration % 3600) // 60)  # 剩余秒数整除一分钟的秒数
        seconds = int(exam_duration % 60)  # 剩余的秒数

        # 格式化为 "X小时X分钟X秒"
        exam_duration = f"{hours}小时{minutes}分钟{seconds}秒"

    dict_user = {
        "name": user.name,
        "user_id": str(user.id),
        "exam_id": str(exam.id),
        "depart": user.depart.name,
        "total_score": total_score,
        "correct_rate": correct_rate,
        "created_at": created_at,
        "exam_duration": exam_duration,
    }

    return dict_user


class DetailGradeNoPage(APIView):
    """
    获取成绩单详情 不分页
    """
    authentication_classes = [JWTAuthentication]
    permission_classes = [AllowAny]

    @swagger_auto_schema(
        operation_description="获取成绩单详情接口",
        request_body=openapi.Schema(
            type=openapi.TYPE_OBJECT,
            required=['username', 'grade_id'],
            properties={
                'username': openapi.Schema(type=openapi.TYPE_STRING, description='用户姓名'),
                'grade_id': openapi.Schema(type=openapi.TYPE_STRING, description='成绩表ID'),
            },
        ),
        responses={
            200: openapi.Response(
                description="成功返回成绩报表和题目正确率",
                schema=DetailGradeSerializer(many=True)
            ),
            400: openapi.Response('请求参数不合法'),
            409: openapi.Response('成绩单详情已存在'),
            404: openapi.Response('成绩单详情不存在'),
        },
        tags=["成绩报表管理"]
    )
    def post(self, request):
        try:
            keywords = request.data.get('username', '')
            grade_id = request.data.get('grade_id')
            grade_profile = Grade.objects.filter(id=grade_id).first()
            if not grade_profile:
                return api_response(ResponseCode.NOT_FOUND, '成绩单不存在')
            exam_id = grade_profile.exam.id

            users_by_keywords = User.objects.filter_by_keywords(keywords)
            exam = Exam.objects.filter(id=exam_id).first()
            if not exam:
                return api_response(ResponseCode.NOT_FOUND, '考试不存在')

            # 用户 成绩 错误率 提交时间 答题用时
            users = []
            data = []

            # 首次考试
            if exam.number == 1:
                exam_departs = ExamDepart.objects.filter(exam=exam)
                for exam_depart in exam_departs:

                    users_profile = User.objects.filter(depart=exam_depart.depart)
                    for username in users_profile:
                        user = User.objects.filter(username=username).first()
                        users.append(user)

            # 补考
            if exam.number > 1:
                users = ExamDepart.objects.filter(exam=exam)

            users_target = list(set(users_by_keywords) & set(users))

            for user in users_target:
                dict_user = get_dict_by_user(user, exam)
                data.append(dict_user)

            serializer = DetailGradeSerializer(data, many=True)

            return api_response(ResponseCode.SUCCESS, '成绩单详情获取成功', serializer.data)
        except ValidationError as e:
            return api_response(ResponseCode.BAD_REQUEST, f"请求参数错误: {str(e)}")
        except Exception as e:
            return api_response(ResponseCode.INTERNAL_SERVER_ERROR, f"服务器内部错误: {str(e)}")
